<%@ page import="java.sql.*" %><%--
用户投过的票
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
    <title>Title</title>
</head>
<body>
<h1 style="color: #3880c2">
    <a href="index.jsp" style="color: crimson">返回主页面</a>
</h1>
<h1 style="color: #3880c2">
    <a href="userPage.jsp" style="color: crimson">返回投票页面</a>
</h1>
<%
    request.setCharacterEncoding("UTF-8");
    int voteId = Integer.parseInt(request.getParameter("id"));
    session.setAttribute("voteId", voteId);
    int userId = Integer.parseInt(session.getAttribute("userId").toString());
%>

<table border bordercolor="#0099ff">
    <tr>
        <th colspan="9">
            <%
                //获取title
                try {
                    Class.forName("com.mysql.cj.jdbc.Driver");
                    Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/mybatis?useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull&serverTimezone=Asia/Shanghai", "root", "123456");
                    //配置了sql方言
                    PreparedStatement preparedStatement0 = connection.prepareStatement("select title from vote where id=?");
                    preparedStatement0.setInt(1, voteId);
                    ResultSet resultSet = preparedStatement0.executeQuery();
                    if (!resultSet.next()) {
                        out.write("暂无数据!");
                    } else {
                        //从查到的数据中取第“1”个
                        out.write(resultSet.getString(1));
                    }
                } catch (Exception e) {
                    out.print(e.getMessage());
                    e.printStackTrace();
                }
            %>
        </th>
    </tr>
    <tr>
        <th bgcolor="#d356e3">选项</th>
        <th bgcolor="#d356e3">得票数</th>
        <th bgcolor="#d356e3">占比</th>
    </tr>
    <%
        //必然能接收到参数id
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/mybatis?useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull&serverTimezone=Asia/Shanghai", "root", "123456");
            //配置了sql方言
            PreparedStatement preparedStatement = connection.prepareStatement("select id,`option`,number from vote_option where vote_id=?");
            //                     第几个问号,参数
            preparedStatement.setInt(1, voteId);
            ResultSet resultSet = preparedStatement.executeQuery();
            int totalNum = 0;
            while (resultSet.next()) {
                totalNum += resultSet.getInt(3);
            }
            System.out.println(totalNum);
            resultSet.close();
            //第一次被用来计算总值了，这里再搜索一次
            resultSet = preparedStatement.executeQuery();
            if (!resultSet.next()) {
                out.println("<tr>" + "<td colspan=\"9\" style=\"color: red\">" + "暂无数据!" + "</th>");
            } else {
                try {
                    //查询全部也可用,先do再while循环
                    //展示所有已经创建投票，只展示标题，附带跳转页面效果，自带id
                    do {
                        int id = resultSet.getInt(1);
                        String option = resultSet.getString(2);
                        int num = resultSet.getInt(3);
                        out.println("<tr>");
                        out.println("<td>" + option + "</td>");
                        out.println("<td>" + "票数:" + num + "</td>");
                        //防止没有任何投票会出现页面显示异常，加上if判断
                        if (0 != totalNum) {
                            out.println("<td>" + num * 100 / totalNum + "%</td>");
                        } else {
                            out.println("暂无投票!</td>");
                        }
                    } while (resultSet.next());
                } catch (Exception e) {
                    out.print(e.getMessage());
                    e.printStackTrace();
                }
            }
            resultSet.close();
            preparedStatement.close();
            connection.close();
        } catch (Exception e) {
            out.print(e.getMessage());
            e.printStackTrace();
        }
    %>


    <%
        //展示你投的是哪一个票
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/mybatis?useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull&serverTimezone=Asia/Shanghai", "root", "123456");
            //这是一个联表查询语句，可以查询到这个用户的这个vote项目中投了哪一个票
            //没有使用问号,都是int类型,还行
            PreparedStatement preparedStatement = connection.prepareStatement("select `option` from vote_option v\n" +
                    "inner join user_option u\n" +
                    //下面语句可以搜到已经投过票的
                    "on u.option_id=v.id\n" +
                    "where u.user_id= " + userId + " and vote_id= " + voteId);
            ResultSet resultSetEd = preparedStatement.executeQuery();
            if (!resultSetEd.next()) {
                System.out.print("错误!@#$!");
                System.out.print("该用户还没有投票这个项目");
            } else {
                String option = resultSetEd.getString(1);
                out.write("你投票的选项:<br>");
                out.write("<a style=\"color: #6969f8; font-size: medium\">" + option + "</a>\n");
            }
            resultSetEd.close();
            preparedStatement.close();
            connection.close();
        } catch (Exception e) {
            out.print(e.getMessage());
            e.printStackTrace();
        }
    %>


</table>
</body>
</html>
